﻿-- =============================================
-- Script Template
-- =============================================


CREATE TABLE dbo.MigracionIds
(
Id bigint NOT NULL IDENTITY (1, 1),
NombreTabla varchar(400) NOT NULL,
IdApInt int  NULL,
IdApStr varchar(50) null,
IdSAEGuid uniqueidentifier NULL,
IdSAEOtro varchar(100) NULL,
[real] [bit] NULL DEFAULT ((0))
)  ON [PRIMARY]
GO
ALTER TABLE dbo.MigracionIds ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED 
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

go

create index IX_MigracionIdsTablaIdInt on dbo.MigracionIds (NombreTabla, IdApInt)
create index IX_MigracionIdsTablaIdStr on dbo.MigracionIds (NombreTabla, IdApStr)

go

CREATE TABLE dbo.DocumentLinking
(
Id uniqueidentifier NOT NULL ,
GuidDocSaeOrigen uniqueidentifier not null,
GuidDocSaeDestino uniqueidentifier null,
GuidAnclaDocDestino uniqueidentifier null,
TipoLink varchar(1) not null,
IdDocAPOrigen int not null,
IdDocAPDestino int  null,
AnclaAP varchar(50) null,
TextoMarcado varchar(300) null,
NumeroArticulo varchar(20) null
)  ON [PRIMARY]
GO
ALTER TABLE dbo.DocumentLinking ADD CONSTRAINT
PK_DocumentLinking PRIMARY KEY CLUSTERED 
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

go

create index IX_DocumentLinking_GuidDocSaeOrigen on DocumentLinking(GuidDocSaeOrigen)
create index IX_DocumentLinking_IdDocAPOrigen on DocumentLinking(IdDocAPOrigen)

alter table DocumentLinking add constraint FK_DocumentLinking_GuidAnclaDocDestino_Id foreign key (GuidAnclaDocDestino) references DocumentLinking(Id)

GO


CREATE TABLE dbo.JurisGrupoSumario
(
Id bigint NOT NULL IDENTITY (1, 1) ,
Procesado bit not null ,
TribunalId int  null,
SalaId int null,
Fecha datetime  null,
Partes varchar(1500) not null
)  ON [PRIMARY]
GO
ALTER TABLE dbo.JurisGrupoSumario ADD CONSTRAINT
PK_JurisGrupoSumario PRIMARY KEY CLUSTERED 
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	
GO

CREATE TABLE dbo.JurisGrupoSumarioDetalle
(
Id bigint NOT NULL IDENTITY (1, 1),
JurisGrupoSumarioId bigint,
IdJJSumario int
)  ON [PRIMARY]

GO
ALTER TABLE dbo.JurisGrupoSumarioDetalle ADD CONSTRAINT
PK_JurisGrupoSumarioDetalle PRIMARY KEY CLUSTERED 
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	
GO

alter table JurisGrupoSumarioDetalle add constraint FK_JurisGrupoSumarioDetalle_JurisGrupoSumario foreign key (JurisGrupoSumarioId) references JurisGrupoSumario(Id)


GO
	

create proc PrepararJurisprudencia
as
begin
delete from JurisGrupoSumarioDetalle
delete from JurisGrupoSumario

-- guardo los sumarios que sean menores a cero
insert into JurisGrupoSumario ([Procesado]
    ,[TribunalId]
    ,[SalaId]
    ,[Fecha]
    ,[Partes])
select 0, jsc.IdTribunal, jsc.IdSala, jsc.Fecha, jsc.Partes
from tJurisSumarioCaratula jsc
group by jsc.IdTribunal, jsc.IdSala, jsc.Fecha, jsc.Partes
having COUNT(IdJJSumario) > 1

-- inserto los valores de los detalles con un cursor
declare @jurisGrupoId bigint
declare @tribunalId int
declare @salaId int
declare @Fecha datetime
declare @Partes varchar(1500)

declare sumariosCursor cursor FORWARD_ONLY READ_ONLY for
select Id, TribunalId, SalaId, Fecha, Partes
from JurisGrupoSumario

open sumariosCursor

fetch next from sumariosCursor into @jurisGrupoId, @tribunalId, @salaId, @Fecha, @Partes

WHILE @@FETCH_STATUS = 0
BEGIN
   
insert into JurisGrupoSumarioDetalle (JurisGrupoSumarioId, IdJJSumario)
select distinct @jurisGrupoId, jsc.IdJJSumario
from tJurisSumarioCaratula jsc
where jsc.IdTribunal = @tribunalId
and   jsc.IdSala = @salaId
and   jsc.Fecha = @Fecha
and   jsc.Partes = @Partes
   
fetch next from sumariosCursor into @jurisGrupoId, @tribunalId, @salaId, @Fecha, @Partes
END

CLOSE sumariosCursor;
DEALLOCATE sumariosCursor;

end


GO

create proc ResetearGruposProcesados
as
begin

update JurisGrupoSumario
set procesado = 0

end

GO

create proc BorrarGruposProcesados
as
begin
delete from JurisGrupoSumarioDetalle
delete from JurisGrupoSumario

end


--create proc MigraExecSql
--@sqlcmd nvarchar(max)
--as
----set @sqlcmd = 'select top 100 * from tlegislacion'
--begin
--exec sp_executesql @sqlcmd
--end
 
 
create table MigraDocLog
(
Id bigint identity(1,1) not null,
ApNombreTabla varchar(300)null,
ApId varchar(100)  null,
GuidSae uniqueidentifier null,
NombreArchivoGenerado varchar(500) null,
Excepcion bit null default(0),
Mensaje varchar(5000) null,       
CONSTRAINT [PK_MigraDocLog] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


use SICLAN

GO
if not exists (select 1 from sys.columns where name = 'CunOnline' and object_id = object_id('TNORMAS'))
begin

alter table TNORMAS add CunOnline varchar(100)

end

GO

update TNORMAS
set cunonline = replace(CUN, ' ', '')

GO

alter table DocumentLinking alter column TextoMarcado varchar(5000)

GO

CREATE TABLE dbo.tLegislacionCcp_Partes(
					id_legislacion int NOT NULL primary key,
					parte_sindical varchar(2000),
					parte_empresaria varchar(2000)
				)

				GO

						

CREATE TABLE dbo.MigraDoctrinaGrupo
(
Id bigint NOT NULL IDENTITY (1, 1) ,
Procesado bit not null ,
IdDDTexto varchar(256) null
)  ON [PRIMARY]
GO
ALTER TABLE dbo.MigraDoctrinaGrupo ADD CONSTRAINT
PK_MigraDoctrinaGrupo PRIMARY KEY CLUSTERED 
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	
GO

CREATE TABLE dbo.MigraDoctrinaGrupoDetalle
(
Id bigint NOT NULL IDENTITY (1, 1),
MigraDoctrinaGrupoId bigint,
IdDDSumario int
)  ON [PRIMARY]

GO
ALTER TABLE dbo.MigraDoctrinaGrupoDetalle ADD CONSTRAINT
PK_MigraDoctrinaGrupoDetalle PRIMARY KEY CLUSTERED 
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	
GO

alter table MigraDoctrinaGrupoDetalle add constraint FK_MigraDoctrinaGrupoDetalle_MigraDoctrinaGrupo foreign key (MigraDoctrinaGrupoId) references MigraDoctrinaGrupo(Id)


